﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;

namespace DVSProject.Lib
{
    public class SetupMachineRepo
    {
        public const string m_msg_head = "SetupMachine";
        public IEnumerable<SetupMachineModels> GetSetupMachine()
        {
            List<SetupMachineModels> setupMachines = new List<SetupMachineModels>();
            string SQLCommand = @"select * from TBSetupMachine where Status <>'Delete' order by LocationSetCode";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        SetupMachineModels setupMachine = new SetupMachineModels();
                        
                        setupMachine.LocationSetCode = (string)reader["LocationSetCode"];
                        setupMachine.SetupDate = (DateTime)reader["SetupDate"];
                        setupMachine.ModelId = (int)reader["ModelId"];
                        setupMachine.Temp = (string)reader["Temp"];
                        setupMachine.SerialNo = (string)reader["SerialNo"];
                        setupMachine.RentBasic = (decimal)reader["RentBasic"];
                        setupMachine.Remark = (string)reader["Remark"];
                        setupMachine.StartMeter = (int)reader["StartMeter"];
                        setupMachine.EndMeter = (int)reader["EndMeter"];
                        setupMachine.TotalMeter = (int)reader["TotalMeter"];
                        setupMachine.Status = (string)reader["Status"];
                        setupMachine.CreateBy = (string)reader["CreateBy"];
                        setupMachine.CreateOn = (DateTime)reader["CreateOn"];
                        setupMachine.UpdateBy = (string)reader["UpdateBy"];
                        setupMachine.UpdateOn = (DateTime)reader["UpdateOn"];

                        setupMachines.Add(setupMachine);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return setupMachines.ToList();
        }

        public  SetupMachineModels  GetSetupMachineByLocationSetCode(string locationSetCode)
        {
            string SQLCommand = @"  
                                    select * from TBSetupMachine where   LocationSetCode=@LocationSetCode";
            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@LocationSetCode", locationSetCode);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        SetupMachineModels setupMachine = new SetupMachineModels();
                        setupMachine.LocationSetCode = (string)reader["LocationSetCode"];
                        setupMachine.SetupDate = (DateTime)reader["SetupDate"];
                        setupMachine.ModelId = (int)reader["ModelId"];
                        setupMachine.Temp = (string)reader["Temp"];
                        setupMachine.SerialNo = (string)reader["SerialNo"];
                        setupMachine.RentBasic = (decimal)reader["RentBasic"];
                        setupMachine.Remark = (string)reader["Remark"];
                        setupMachine.StartMeter = (int)reader["StartMeter"];
                        setupMachine.EndMeter = (int)reader["EndMeter"];
                        setupMachine.TotalMeter = (int)reader["TotalMeter"];
                        setupMachine.Status = (string)reader["Status"];
                        setupMachine.CreateBy = (string)reader["CreateBy"];
                        setupMachine.CreateOn = (DateTime)reader["CreateOn"];
                        setupMachine.UpdateBy = (string)reader["UpdateBy"];
                        setupMachine.UpdateOn = (DateTime)reader["UpdateOn"];

                        return setupMachine;
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return null;
        }

        public SetupMachineModels GetSetupMachineByLocationSetCodeForChange(string locationSetCode)
        {
            string SQLCommand = @"  insert into   TBSetupMachineTemp 
                                        select LocationSetCode, SetupDate, ModelId,Temp,
                                        SerialNo,RentBasic,Remark,StartMeter,
                                        EndMeter,TotalMeter,Status,CreateBy ,
                                        CreateOn ,UpdateBy ,UpdateON 
                                  from TBSetupMachine
                                  where  LocationSetCode=@LocationSetCode

                                    select * from TBSetupMachine where   LocationSetCode=@LocationSetCode";
            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@LocationSetCode", locationSetCode);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        SetupMachineModels setupMachine = new SetupMachineModels();
                        setupMachine.LocationSetCode = (string)reader["LocationSetCode"];
                        setupMachine.SetupDate = (DateTime)reader["SetupDate"];
                        setupMachine.ModelId = (int)reader["ModelId"];
                        setupMachine.Temp = (string)reader["Temp"];
                        setupMachine.SerialNo = (string)reader["SerialNo"];
                        setupMachine.RentBasic = (decimal)reader["RentBasic"];
                        setupMachine.Remark = (string)reader["Remark"];
                        setupMachine.StartMeter = (int)reader["StartMeter"];
                        setupMachine.EndMeter = (int)reader["EndMeter"];
                        setupMachine.TotalMeter = (int)reader["TotalMeter"];
                        setupMachine.Status = (string)reader["Status"];
                        setupMachine.CreateBy = (string)reader["CreateBy"];
                        setupMachine.CreateOn = (DateTime)reader["CreateOn"];
                        setupMachine.UpdateBy = (string)reader["UpdateBy"];
                        setupMachine.UpdateOn = (DateTime)reader["UpdateOn"];

                        return setupMachine;
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return null;
        }

        public bool Create(SetupMachineModels setupMachine)
        {
            bool result = true;
            string SQLCommand = @"INSERT into TBSetupMachineTemp(LocationSetCode,Status,CreateBy,CreateOn,UpdateBy,UpdateOn)
                                VALUES(@LocationSetCode,@Status,@UserId,getdate(),@UserId,getdate())";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@LocationSetCode", setupMachine.LocationSetCode);
                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID", setupMachine.CreateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }


        public bool Setup(SetupMachineModels setupMachine)
        {
            bool result = true;
            string SQLCommand = @"declare @Id int
                                    select @Id= max(id) from TBSetupMachineTemp 
                                    where createby =@UserID

                                update TBSetupMachineTemp
                                set SetupDate =getdate(),
                                ModelId =@ModelId,
                                Temp =@Temp,
                                SerialNo=@SerialNo,
                                RentBasic=@RentBasic,
                                Remark=@Remark,
                                StartMeter=@StartMeter,
                                UpdateBy=@UserId,
	                            UpdateOn=GETDATE()
                                where Id=@Id

                             insert into   TBSetupMachine 
                                select LocationSetCode, SetupDate, ModelId,Temp,
                                SerialNo,RentBasic,Remark,StartMeter,
                                EndMeter,TotalMeter,Status,CreateBy ,
                                CreateOn ,UpdateBy ,UpdateON 
                             from TBSetupMachineTemp
                             where Id=@Id
        
                            update TBSetupMachineTemp
                            set Status ='Complete'
                            where Id=@Id

                            -- calculate discount 
                        DECLARE @Year int 
                        DECLARE @Discount int
                        SELECT @Year=DATEDIFF(YEAR,SetupDate,GETDATE()) from TBSetupMachinetemp where id =1

                        set @Discount =0
                        if @Year >5 and @Year <=8   
                        begin 
	                        set @Discount=36
                        END 

                        if @Year >8   
                        begin 
	                        set @Discount=50
                        END 

                            insert into   TBSetupMachineLog (LocationSetCode,SetupDate,ModelId,Temp,SerialNo,RentBasic,
                                        Remark,StartMeter,EndMeter,TotalMeter,discount,Status,CreateBy,CreateOn,UpdateBy,UpdateON)
                             select LocationSetCode, SetupDate, ModelId,Temp,
                                SerialNo,RentBasic,Remark,StartMeter,
                                EndMeter,TotalMeter,@Discount,Status,CreateBy ,
                                CreateOn ,UpdateBy ,UpdateON 
                             from TBSetupMachineTemp
                             where Id=@Id
                                ";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@ModelId", setupMachine.ModelId);
                    db.AddParameter("@Temp", setupMachine.Temp);
                    db.AddParameter("@SerialNo", setupMachine.SerialNo);
                    db.AddParameter("@RentBasic", setupMachine.RentBasic);
                    db.AddParameter("@Remark", setupMachine.Remark);
                    db.AddParameter("@StartMeter", setupMachine.StartMeter);
                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID", setupMachine.CreateBy );

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }
    }
}